//
//  SQLHandler.m
//  echo
//
//  Created by dllo on 16/3/2.
//  Copyright © 2016年 Danny. All rights reserved.
//

#import "SQLHandler.h"
#import <sqlite3.h>
#import "ModelOfCellForMyExploreTableViewSectionThreeAndFour.h"

@implementation SQLHandler

+ (instancetype)shareSQLTable{
    static SQLHandler *sqlHandler = nil;
    if (sqlHandler == nil) {
        sqlHandler = [[SQLHandler alloc]init];
    }
    return sqlHandler;
}

sqlite3 *db;

+ (void)openSQL{
    if (db != nil) {
        NSLog(@"数据库已经开启!!!");
        return;
    }
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject] stringByAppendingPathComponent:@"echo.sqlite"];
    
    int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"数据库打开成功!");
        NSLog(@"沙盒路径:%@", NSHomeDirectory());
    }else{
        
        NSLog(@"数据库打开失败, code = %d", result);
    }
}

+ (void)openSQLForClassifyVC{
    if (db != nil) {
        NSLog(@"数据库已经开启!!!");
        return;
    }
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject] stringByAppendingPathComponent:@"echo2.sqlite"];
    
    int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"数据库打开成功!");
    }else{
        
        NSLog(@"数据库打开失败, code = %d", result);
    }
}

+ (void)createSQLTable{
    
    NSString *strSQL = @"CREATE TABLE IF NOT EXISTS echo(number INTEGER PRIMARY KEY AUTOINCREMENT, avatar text, famous_icon text, name text, content text)";
    
    int result = sqlite3_exec(db, strSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建表格完成!");
    }else{

        NSLog(@"创建表格失败.code:%d", result);
    }

}

+ (void)createSQLTableForClassifyVC{
    
    NSString *strSQL = @"CREATE TABLE IF NOT EXISTS echo2(number INTEGER PRIMARY KEY AUTOINCREMENT, avatar text, famous_icon text, name text, content text)";
    
    int result = sqlite3_exec(db, strSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
//        NSLog(@"创建表格完成!");
    }else{
        
        NSLog(@"创建表格失败.code:%d", result);
    }
    
}

- (void)insertData:(ModelOfCellForMyExploreTableViewSectionThreeAndFour *)model{
    
    NSString *strSQL = [NSString stringWithFormat:@"INSERT into echo(avatar, famous_icon, name, content) VALUES('%@', '%@', '%@', '%@')", model.avatar, model.famous_icon, model.name, model.content];
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"添加数据完成!");
    }else{
        
        NSLog(@"添加数据失败.code:%d", result);
    }
}

- (void)insertDataForClassifyVC:(NSMutableDictionary *)model{
    
    NSString *strSQL = [NSString stringWithFormat:@"INSERT into echo2(avatar, famous_icon, name, content) VALUES('%@', '%@', '%@', '%@')", [model objectForKey:@"avatar"], [model objectForKey:@"famous_icon"], [model objectForKey:@"name"], [model objectForKey:@"content"]];
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"添加数据完成!");
    }else{
        
        NSLog(@"添加数据失败.code:%d", result);
    }
}

+ (void)updateData:(ModelOfCellForMyExploreTableViewSectionThreeAndFour *)model forNumber:(NSInteger)number{
    
    NSString *strSQL = [NSString stringWithFormat:@"UPDATE echo SET avatar = '%@', famous_icon = '%@', name = '%@', content = '%@' WHERE number = '%ld'", model.avatar, model.famous_icon, model.name, model.content, (long)number];
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"更新成功");
    }else{
        
        NSLog(@"更新失败, code:%d", result);
    }
}

+ (void)updateDataForClassifyVC:(NSMutableDictionary *)model forNumber:(NSInteger)number{
    
    NSString *strSQL = [NSString stringWithFormat:@"UPDATE echo2 SET avatar = '%@', famous_icon = '%@', name = '%@', content = '%@' WHERE number = '%ld'", [model objectForKey:@"avatar"], [model objectForKey:@"famous_icon"], [model objectForKey:@"name"], [model objectForKey:@"content"], (long)number];
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"更新成功");
    }else{
        
        NSLog(@"更新失败, code:%d", result);
    }
}

+ (void)deleteData:(NSString *)name{
    
    NSString *strSQL = [NSString stringWithFormat:@"DELETE FROM echo WHERE name = '%@'", name];
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除成功!");
    }else{

        NSLog(@"删除失败. code:%d", result);
    }

}

+ (void)deleteDataForClassifyVC:(NSString *)name{
    
    NSString *strSQL = [NSString stringWithFormat:@"DELETE FROM echo2 WHERE name = '%@'", name];
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除成功!");
    }else{
        
        NSLog(@"删除失败. code:%d", result);
    }
    
}

+ (NSArray *)searchData{
    
    NSMutableArray *arr = [NSMutableArray array];
    NSString *strSQL = [NSString stringWithFormat:@"SELECT * FROM echo"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, strSQL.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"准备就绪, 查询中......");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *avatar= sqlite3_column_text(stmt, 1);
            const unsigned char *famous_icon = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            const unsigned char *content = sqlite3_column_text(stmt, 4);
            
            ModelOfCellForMyExploreTableViewSectionThreeAndFour *model = [[ModelOfCellForMyExploreTableViewSectionThreeAndFour alloc]init];
            model.avatar = [NSString stringWithUTF8String:(const char*)avatar];
            model.famous_icon = [NSString stringWithUTF8String:(const char*)famous_icon];
            model.name = [NSString stringWithUTF8String:(const char*)name];
            model.content = [NSString stringWithUTF8String:(const char*)content];

            [arr addObject:model];
        }
        sqlite3_finalize(stmt);
        
    }else{
        NSLog(@"不能正常查询. code:%d", result);
        // 销毁对象
        sqlite3_finalize(stmt);

    }
    return arr;
}

+ (NSArray *)searchDataForClassifyVC{
    
    NSMutableArray *arr = [NSMutableArray array];
    NSString *strSQL = [NSString stringWithFormat:@"SELECT * FROM echo2"];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, strSQL.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
//        NSLog(@"准备就绪, 查询中......");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *avatar= sqlite3_column_text(stmt, 1);
            const unsigned char *famous_icon = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            const unsigned char *content = sqlite3_column_text(stmt, 4);
            
            ModelOfCellForMyExploreTableViewSectionThreeAndFour *model = [[ModelOfCellForMyExploreTableViewSectionThreeAndFour alloc]init];
            model.avatar = [NSString stringWithUTF8String:(const char*)avatar];
            model.famous_icon = [NSString stringWithUTF8String:(const char*)famous_icon];
            model.name = [NSString stringWithUTF8String:(const char*)name];
            model.content = [NSString stringWithUTF8String:(const char*)content];
            
            [arr addObject:model];
        }
        sqlite3_finalize(stmt);
        
    }else{
        NSLog(@"不能正常查询. code:%d", result);
        // 销毁对象
        sqlite3_finalize(stmt);
        
    }
    return arr;
}

+ (void)deleteTable{
    
    NSString *strSQL = @"DROP TABLE echo";
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除表格成功!!!");
    }else{
        
        NSLog(@"删除表格失败. code:%d", result);
    }
}

+ (void)deleteTableForClassifyVC{
    
    NSString *strSQL = @"DROP TABLE echo2";
    
    int result = sqlite3_exec(db, strSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"删除表格成功!!!");
    }else{
        
        NSLog(@"删除表格失败. code:%d", result);
    }
}

+ (void)closeSQL{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        
        NSLog(@"数据库关闭成功!!!");
        db = nil;
    }else{
        
        NSLog(@"数据库关闭失败. code:%d", result);
    }
}

@end
